package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.*;

public class DBCustomer {
	private Connection con;
	
	public DBCustomer(){
		con = DbConnection.getInstance().getDBcon();
	}
	
	public void insertCostumer(Customer customer){
		   
		//creates a new customer in the database
		String query ="INSERT INTO customer(fname, lname, customerAddress, zipCode, country, phoneNo, email, customerType, regAccount, accountNo) " +
				"VALUES('"+
				customer.getFname()  + "','"  +
				customer.getLname()  + "','"  +
				customer.getCustomerAddress()  + "','"  +
				customer.getZipCode()  + "','"  +
				customer.getCountry()  + "','"  +
				customer.getPhoneNo()  + "','"  +
				customer.getEmail()  + "','"  +
				customer.getCustomerType()  + "','"  +
				customer.getRegAccount()  + "','"  +
				customer.getAccountNo()  + "')";
		
	}
	public int updateCustomer(Customer cus)
	{
		Customer cusObj  = cus;
		int rc=-1;

		String query="UPDATE Customer SET "+
		 	  "fname ='"+ cusObj.getFname()+"', "+
		 	  "lname ='"+ cusObj.getLname() + "', " +
              "adress ='"+ cusObj.getCustomerAddress() + "', " +
              "zip code ='"+ cusObj.getZipCode() + "', " +
              "country ='"+ cusObj.getCountry() + "', " +
              "email ='"+ cusObj.getEmail() + "', " +
              "customer type  ='"+ cusObj.getCustomerType() + "', " +
              "reg number='"+ cusObj.getRegAccount() + "', " +
              "account number='"+ cusObj.getAccountNo()+ "', " +
		          " WHERE phoneNo = '"+ cusObj.getPhoneNo() + "'";
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	public Customer findCustomer(String attValue, boolean retriveAssociation) {
	    {
	        String wClause = "phoneNo = '" + attValue + "'";
	        System.out.println("SearchEmployye " + wClause);
	        return singleWhere(wClause, retriveAssociation);
	    }
	}
	private String buildQuery(String wClause)
	{
	    String query="SELECT fname, lname, customerAddress, zipCode, country, phoneNo, email, customerType, regAccount, accountNo FROM customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	
	
	//method to select one product
	private Customer singleWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		Customer cObj = new Customer(wClause, wClause, wClause, wClause, wClause, wClause, wClause, wClause);
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		//read the customer from the database
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		
		if(results.next())
		{
			cObj = buildCustomer(results);
			stmt.close();
		}
		else
		{
			cObj = null;
		}
	//get all Customer
    public ArrayList<Customer> getAllCustomers(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
	private ArrayList<Customer> miscWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		ArrayList<Customer> list = new ArrayList<Customer>();
		
		//read the product from the database
		String query = buildQuery(wClause);
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		results = stmt.executeQuery(query);
		
		while(results.next())
		{
			Customer cObj = new Customer(query, query, query, query, query, query, query, query);
			cObj = buildCustomer(results);
			list.add(cObj);
		}
		
		stmt.close();
		return list;
	}
    



	
	
}